package com.controller;

import com.bean.Prisoner;
import com.bean.Pwork;
import com.bean.Visitor;
import com.bean.Worker;
import com.util.BaseDao;
import com.util.CustomDateConverter;
import com.util.CustomLocalTimeConverter;
import javafx.application.Platform;
import javafx.beans.property.IntegerProperty;
import javafx.beans.property.SimpleIntegerProperty;
import javafx.beans.property.SimpleStringProperty;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.scene.control.*;
import javafx.scene.control.cell.CheckBoxTableCell;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.control.cell.TextFieldTableCell;
import javafx.util.Callback;
import javafx.util.StringConverter;
import javafx.util.converter.IntegerStringConverter;

import java.sql.*;
import java.time.LocalDate;
import java.time.LocalTime;
import java.time.format.DateTimeParseException;
import java.util.ArrayList;
import java.util.List;


import static com.Main.changeUI;

public class MainInterface {
    //显示功能表
    @FXML
    private TableView<Prisoner> tableView1;
    @FXML
    private TableView<Worker> tableView2;
    @FXML
    private TableView<Visitor> VisitorTable;
    @FXML
    private TableView<Pwork> tableViewPW;
    //添加功能表
    @FXML
    private TableView<Worker> tableView3;
    @FXML
    private TableView<Visitor> VisitorTable1;
    @FXML
    private TableView<Prisoner> tableView11;
    @FXML
    private TableView<Pwork> tableViewPWadd;

    @FXML
    private TableColumn<Worker, String> w_no;
    @FXML
    private TableColumn<Worker, String> w_name;
    @FXML
    private TableColumn<Worker, String> w_sex;
    @FXML
    private TableColumn<Worker, String> w_id;
    @FXML
    private TableColumn<Worker, LocalDate> w_birth;
    @FXML
    private TableColumn<Worker, String> w_work;
    @FXML
    private TableColumn<Worker, String> w_position;
    @FXML
    private TableColumn<Worker, Integer> w_wage;


    @FXML
    private TableColumn<Visitor, String> v_no;
    @FXML
    private TableColumn<Visitor, String> v_name;
    @FXML
    private TableColumn<Visitor, LocalDate> v_date;
    @FXML
    private TableColumn<Visitor, String> v_pno;
    @FXML
    private TableColumn<Visitor, String> v_wno;

    @FXML
    private TableColumn<Prisoner, String> p_no;
    @FXML
    private TableColumn<Prisoner, String> p_name;
    @FXML
    private TableColumn<Prisoner, String> p_sex;
    @FXML
    private TableColumn<Prisoner, String> p_id;
    @FXML
    private TableColumn<Prisoner, LocalDate> p_birth;
    @FXML
    private TableColumn<Prisoner, String> p_crime;
    @FXML
    private TableColumn<Prisoner, LocalDate> p_intime;
    @FXML
    private TableColumn<Prisoner, LocalDate> p_outtime;
    @FXML
    private TableColumn<Prisoner, String> p_postion;

    @FXML
    private TableColumn<Pwork, String> pw_name;
    @FXML
    private TableColumn<Pwork, String> pw_pno;
    @FXML
    private TableColumn<Pwork, String> pw_postion;
    @FXML
    private TableColumn<Pwork, String> pw_wtime;
    @FXML
    private TableColumn<Pwork, LocalDate> pw_begin;
    @FXML
    private TableColumn<Pwork, LocalDate> pw_out;


    @FXML
    private Button wbuttonadd;
    @FXML
    private Button Wsubmit;
    @FXML
    private Button WfthisShow;


    @FXML
    private Button Vbuttonadd1;
    @FXML
    private Button Vsubmit;
    @FXML
    private Button VfthisShow;


    @FXML
    private Button pbuttonadd;
    @FXML
    private Button Psubmit;
    @FXML
    private Button PfthisShow;


    @FXML
    private Button pwbuttonadd;
    @FXML
    private Button PWsubmit;
    @FXML
    private Button PWfthisShow;

    @FXML
    private Button WbuttonDelete;

    @FXML
    private Button PWbuttonDelete;

    @FXML
    private Button pbuttonDelete;

    @FXML
    private Button VbuttonDelete;

    @FXML
    private Button Pselect;
    @FXML
    private Button Wselect;
    @FXML
    private Button Vselect;

    @FXML
    private TextField selectField;


    //显示表的数据源
    private ObservableList<Worker> items;
    private ObservableList<Prisoner> pitems;
    private ObservableList<Visitor> vitems;
    private ObservableList<Pwork> pwitems;
    //添加表的数据源
    private ObservableList<Worker> items1;
    private ObservableList<Prisoner> pitems2;
    private ObservableList<Visitor> vitems2;
    private ObservableList<Pwork> pwitems2;

    private static final String URL = "jdbc:sqlserver://192.168.31.227:1433;databaseName=master;encrypt=true;trustServerCertificate=true;";
    private static final String USER = "sa";
    private static final String PASSWORD = "xuchenjie";


    public void initialize() {


        Pinitialize();

        PWinitialize();


        Winitialize();


        Vinitialize();

        Pselect.setVisible(false);
        Wselect.setVisible(false);
        Vselect.setVisible(false);

    }

    public void Winitialize() {
        //接受数据库信息
        items = FXCollections.observableArrayList();
        items1 = FXCollections.observableArrayList();
        loadDataFromDatabase();

        //信息显示在View2中
        tableView2.setItems(items);
        //tableView2.getProperties().put("JavaFX Application Thread", true);
        showTable();
        tableView2.setVisible(true);

        setupTableView();
        for (Worker work : items) {
            work.setSelected(false); // 设置默认未选中
        }

    }

    public void Pinitialize() {
        //接受数据库信息
        pitems = FXCollections.observableArrayList();
        pitems2 = FXCollections.observableArrayList();
        loadPDataFromDatabase();

        //信息显示在View1中
        tableView1.setItems(pitems);
        //tableView2.getProperties().put("JavaFX Application Thread", true);
        showPTable();
        tableView1.setVisible(true);

        setupPTableView();
        for (Prisoner prisoner : pitems) {
            prisoner.setSelected(false); // 设置默认未选中
        }

    }

    public void PWinitialize() {
        //接受数据库信息
        pwitems = FXCollections.observableArrayList();
        pwitems2 = FXCollections.observableArrayList();
        loadPWDataFromDatabase();

        //信息显示在View1中
        tableViewPW.setItems(pwitems);
        //tableView2.getProperties().put("JavaFX Application Thread", true);
        showPWTable();
        tableViewPW.setVisible(true);

        setupPWTableView();
        for (Pwork pwork : pwitems) {
            pwork.setSelected(false); // 设置默认未选中
        }

    }

    public void Vinitialize() {
        //接受数据库信息
        vitems = FXCollections.observableArrayList();
        vitems2 = FXCollections.observableArrayList();
        loadVDataFromDatabase();

        //信息显示在View2中
        VisitorTable.setItems(vitems);
        //tableView2.getProperties().put("JavaFX Application Thread", true);
        showVTable();
        VisitorTable.setVisible(true);

        setupVTableView();
        for (Visitor visitor : vitems) {
            visitor.setSelected(false); // 设置默认未选中
        }

    }

    @FXML
    public void doWadd() {
        //清除添加项目的信息
        items1.clear();

        //将items1绑定到table3
        tableView3.setItems(items1);
        for (int i = 0; i < 10; i++) {
            Worker newWorker = new Worker();
            items1.add(newWorker);
        }

        tableView3.setVisible(true);
        Wsubmit.setVisible(true);
        WfthisShow.setVisible(true);
        tableView3.setEditable(true);
        if (tableView3.getColumns().isEmpty()) {
            setupTableView();
        }

    }

    @FXML
    void doVadd() {
        //清除添加项目的信息
        vitems2.clear();

        //将items1绑定到table3
        VisitorTable1.setItems(vitems2);
        for (int i = 0; i < 10; i++) {
            Visitor newVisitor = new Visitor();
            vitems2.add(newVisitor);
        }

        VisitorTable1.setVisible(true);
        Vsubmit.setVisible(true);
        VfthisShow.setVisible(true);
        VisitorTable1.setEditable(true);
        if (VisitorTable1.getColumns().isEmpty()) {
            setupVTableView();
        }

    }

    @FXML
    void doPadd() {

        //清除添加项目的信息
        pitems2.clear();

        //将items1绑定到table3
        tableView11.setItems(pitems2);
        for (int i = 0; i < 10; i++) {
            Prisoner newPrisoner = new Prisoner();
            pitems2.add(newPrisoner);
        }

        tableView11.setVisible(true);
        Psubmit.setVisible(true);
        PfthisShow.setVisible(true);
        tableView11.setEditable(true);
        if (tableView11.getColumns().isEmpty()) {
            setupPTableView();
        }

    }

    @FXML
    void doPWadd() {
        //清除添加项目的信息
        pwitems2.clear();

        //将items1绑定到table3
        tableViewPWadd.setItems(pwitems2);
        for (int i = 0; i < 10; i++) {
            Pwork newPwork = new Pwork();
            pwitems2.add(newPwork);
        }

        tableViewPWadd.setVisible(true);
        PWsubmit.setVisible(true);
        PWfthisShow.setVisible(true);
        tableViewPWadd.setEditable(true);
        if (tableViewPWadd.getColumns().isEmpty()) {
            setupPWTableView();
        }

    }

    @FXML
    public void doWSubmit() {
        // 确保UI更新操作在JavaFX应用程序线程上执行
        Platform.runLater(() -> {
            boolean isInsertSuccess = true;
            try {
                // 获取数据库连接
                Connection connection = BaseDao.getConn();

                // 遍历items1中的每个Worker对象
                for (Worker worker : items1) {
                    if (worker.getWno() == null || worker.getWname() == null || worker.getSex() == null
                            || worker.getIdcard() == null || worker.getBirthday() == null || worker.getJobtype() == null
                            || worker.getPostion() == null || worker.getWages() == null) {
                        continue;
                    }
                    // 构建SQL插入语句
                    String sql = "INSERT INTO worker (wno, wname, sex, idcard, birthday, jobtype, postion, wages) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
                    try (PreparedStatement stmt = connection.prepareStatement(sql)) {
                        // 设置预处理语句的参数
                        stmt.setString(1, worker.getWno());
                        stmt.setString(2, worker.getWname());
                        stmt.setString(3, worker.getSex());
                        stmt.setString(4, worker.getIdcard());
                        stmt.setDate(5, java.sql.Date.valueOf(worker.getBirthday()));
                        stmt.setString(6, worker.getJobtype());
                        stmt.setString(7, worker.getPostion());
                        stmt.setInt(8, worker.getWages());

                        // 执行插入操作
                        stmt.executeUpdate();
                    } catch (SQLException e) {
                        // 处理SQL异常，打印堆栈跟踪信息
                        e.printStackTrace();
                        // 这里不需要回滚事务，因为自动提交模式是开启的
                    }
                }
                if (isInsertSuccess) {
                    // 如果所有记录都成功插入，则弹出提示信息
                    Alert alert = new Alert(Alert.AlertType.INFORMATION, "添加成功", ButtonType.OK);
                    alert.showAndWait();
                } else {
                    Alert alert = new Alert(Alert.AlertType.INFORMATION, "添加失败", ButtonType.OK);
                    alert.showAndWait();
                }

                // 刷新tableView2以显示更新的数据
                loadDataFromDatabase();
                // tableView2.refresh();

                // 可选：更新tableView3为不可编辑状态，或其他需要的状态更新
                tableView3.setEditable(false);

            } catch (Exception e) {
                // 打印任何其他异常的堆栈跟踪信息
                e.printStackTrace();
            }
        });
    }

    @FXML
    public void doVSubmit() {
        // 确保UI更新操作在JavaFX应用程序线程上执行
        Platform.runLater(() -> {
            boolean isInsertSuccess = true;
            try {
                // 获取数据库连接
                Connection connection = BaseDao.getConn();

                // 遍历items1中的每个Worker对象
                for (Visitor visitor : vitems2) {
                    if (visitor.getVno() == null || visitor.getVname() == null || visitor.getVadate() == null
                            || visitor.getPno() == null || visitor.getWno() == null) {
                        continue;
                    }
                    // 构建SQL插入语句
                    String sql = "INSERT INTO visitor (vno, vname, vdate, pno, wno) VALUES (?, ?, ?, ?, ?)";
                    try (PreparedStatement stmt = connection.prepareStatement(sql)) {
                        // 设置预处理语句的参数
                        stmt.setString(1, visitor.getVno());
                        stmt.setString(2, visitor.getVname());
                        stmt.setDate(3, java.sql.Date.valueOf(visitor.getVadate()));
                        stmt.setString(4, visitor.getPno());
                        stmt.setString(5, visitor.getWno());

                        // 执行插入操作
                        stmt.executeUpdate();
                    } catch (SQLException e) {
                        // 处理SQL异常，打印堆栈跟踪信息
                        e.printStackTrace();
                        isInsertSuccess = false;
                        // 这里不需要回滚事务，因为自动提交模式是开启的
                    }
                }
                if (isInsertSuccess) {
                    // 如果所有记录都成功插入，则弹出提示信息
                    Alert alert = new Alert(Alert.AlertType.INFORMATION, "添加成功", ButtonType.OK);
                    alert.showAndWait();
                } else {
                    Alert alert = new Alert(Alert.AlertType.INFORMATION, "添加失败", ButtonType.OK);
                    alert.showAndWait();
                }

                // 刷新tableView2以显示更新的数据
                loadVDataFromDatabase();
                // tableView1.refresh();

                // 可选：更新tableView3为不可编辑状态，或其他需要的状态更新
                VisitorTable1.setEditable(false);

            } catch (Exception e) {
                // 打印任何其他异常的堆栈跟踪信息
                e.printStackTrace();
            }
        });


    }

    @FXML
    public void doPSubmit() {
        // 确保UI更新操作在JavaFX应用程序线程上执行
        Platform.runLater(() -> {
            boolean isInsertSuccess = true;
            try {
                // 获取数据库连接
                Connection connection = BaseDao.getConn();

                // 遍历items1中的每个Worker对象
                for (Prisoner prisoner : pitems2) {
                    if (prisoner.getPno() == null || prisoner.getPname() == null || prisoner.getSex() == null
                            || prisoner.getIdcard() == null || prisoner.getBirthdayTime() == null || prisoner.getCrime() == null
                            || prisoner.getIntime() == null || prisoner.getOuttime() == null || prisoner.getPostion() == null) {
                        continue;
                    }
                    // 构建SQL插入语句
                    String sql = "INSERT INTO prisoner (pno, pname, sex, idcard, birthday, crime, intime, outtime,postion) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?)";
                    try (PreparedStatement stmt = connection.prepareStatement(sql)) {
                        // 设置预处理语句的参数
                        stmt.setString(1, prisoner.getPno());
                        stmt.setString(2, prisoner.getPname());
                        stmt.setString(3, prisoner.getSex());
                        stmt.setString(4, prisoner.getIdcard());
                        stmt.setDate(5, java.sql.Date.valueOf(prisoner.getBirthdayTime()));
                        stmt.setString(6, prisoner.getCrime());
                        stmt.setDate(7, java.sql.Date.valueOf(prisoner.getIntime()));
                        stmt.setDate(8, java.sql.Date.valueOf(prisoner.getOuttime()));
                        stmt.setString(9, prisoner.getPostion());

                        // 执行插入操作
                        stmt.executeUpdate();
                    } catch (SQLException e) {
                        // 处理SQL异常，打印堆栈跟踪信息
                        e.printStackTrace();
                        isInsertSuccess = false;
                        // 这里不需要回滚事务，因为自动提交模式是开启的
                    }
                }
                if (isInsertSuccess) {
                    // 如果所有记录都成功插入，则弹出提示信息
                    Alert alert = new Alert(Alert.AlertType.INFORMATION, "添加成功", ButtonType.OK);
                    alert.showAndWait();
                } else {
                    Alert alert = new Alert(Alert.AlertType.INFORMATION, "添加失败", ButtonType.OK);
                    alert.showAndWait();
                }

                // 刷新tableView2以显示更新的数据
                loadPDataFromDatabase();
                // tableView1.refresh();

                // 可选：更新tableView3为不可编辑状态，或其他需要的状态更新
                tableView11.setEditable(false);

            } catch (Exception e) {
                // 打印任何其他异常的堆栈跟踪信息
                e.printStackTrace();
            }
        });
    }

    @FXML
    void doPWSubmit() {
        // 确保UI更新操作在JavaFX应用程序线程上执行
        Platform.runLater(() -> {
            boolean isInsertSuccess = true;
            try {
                // 获取数据库连接
                Connection connection = BaseDao.getConn();

                // 遍历items1中的每个Worker对象
                for (Pwork pwork : pwitems2) {
                    if (pwork.getPworkname() == null || pwork.getPno() == null || pwork.getPworkposion() == null
                            || pwork.getWtime() == null || pwork.getBegintime() == null || pwork.getOuttime() == null) {
                        continue;
                    }
                    // 构建SQL插入语句
                    String sql = "INSERT INTO Pwork (pworkname, pno, pworkpostion, wtime, begintime, outtime) VALUES (?, ?, ?, ?, ?, ?)";
                    try (PreparedStatement stmt = connection.prepareStatement(sql)) {
                        // 设置预处理语句的参数
                        stmt.setString(1, pwork.getPworkname());
                        stmt.setString(2, pwork.getPno());
                        stmt.setString(3, pwork.getPworkposion());
                        stmt.setString(4, pwork.getWtime());
                        stmt.setDate(5, java.sql.Date.valueOf(pwork.getBegintime()));
                        stmt.setDate(6, java.sql.Date.valueOf(pwork.getOuttime()));

                        // 执行插入操作
                        stmt.executeUpdate();
                    } catch (SQLException e) {
                        // 处理SQL异常，打印堆栈跟踪信息
                        e.printStackTrace();
                        isInsertSuccess = false;
                        // 这里不需要回滚事务，因为自动提交模式是开启的
                    }
                }
                if (isInsertSuccess) {
                    // 如果所有记录都成功插入，则弹出提示信息
                    Alert alert = new Alert(Alert.AlertType.INFORMATION, "添加成功", ButtonType.OK);
                    alert.showAndWait();
                } else {
                    Alert alert = new Alert(Alert.AlertType.INFORMATION, "添加失败", ButtonType.OK);
                    alert.showAndWait();
                }

                // 刷新tableView2以显示更新的数据
                loadPWDataFromDatabase();
                // tableView1.refresh();

                // 可选：更新tableView3为不可编辑状态，或其他需要的状态更新
                tableViewPWadd.setEditable(false);

            } catch (Exception e) {
                // 打印任何其他异常的堆栈跟踪信息
                e.printStackTrace();
            }
        });

    }


    private void setupTableView() {

        w_no.setCellValueFactory(new PropertyValueFactory<>("wno"));
        w_no.setCellFactory(TextFieldTableCell.forTableColumn());
        w_no.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            Worker worker = event.getRowValue();
            worker.setWno(event.getNewValue());

        });

        w_name.setCellValueFactory(new PropertyValueFactory<>("wname"));
        w_name.setCellFactory(TextFieldTableCell.forTableColumn());
        w_name.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的w_no属性
            Worker worker = event.getRowValue();
            worker.setWname(event.getNewValue());
        });

        w_sex.setCellValueFactory(new PropertyValueFactory<>("sex"));
        w_sex.setCellFactory(TextFieldTableCell.forTableColumn());
        w_sex.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的w_no属性
            Worker worker = event.getRowValue();
            worker.setSex(event.getNewValue());
        });

        w_id.setCellValueFactory(new PropertyValueFactory<>("idcard"));
        w_id.setCellFactory(TextFieldTableCell.forTableColumn());
        w_id.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的w_no属性
            Worker worker = event.getRowValue();
            worker.setIdcard(event.getNewValue());
        });

        w_birth.setCellValueFactory(new PropertyValueFactory<>("birthday"));
        CustomDateConverter converter = new CustomDateConverter();
        w_birth.setCellFactory(TextFieldTableCell.forTableColumn(converter));
        w_birth.setOnEditCommit(event -> {
            Worker worker = event.getRowValue();
            LocalDate newBirthDate = converter.fromString(event.getNewValue().toString());
            worker.setBirthday(newBirthDate);
        });

        w_work.setCellValueFactory(new PropertyValueFactory<>("jobtype"));
        w_work.setCellFactory(TextFieldTableCell.forTableColumn());
        w_work.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的w_no属性
            Worker worker = event.getRowValue();
            worker.setJobtype(event.getNewValue());
        });
        w_position.setCellValueFactory(new PropertyValueFactory<>("postion"));
        w_position.setCellFactory(TextFieldTableCell.forTableColumn());
        w_position.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的w_no属性
            Worker worker = event.getRowValue();
            worker.setPostion(event.getNewValue());
        });
        w_wage.setCellValueFactory(new PropertyValueFactory<>("wages"));

        StringConverter<Integer> converter1 = new IntegerStringConverter();

        w_wage.setCellFactory(TextFieldTableCell.forTableColumn(converter1));
        w_wage.setOnEditCommit(event -> {
            if (event.getNewValue() != null) {
                try {
                    Worker worker = event.getRowValue();
                    // 尝试将新值转换为 Integer
                    int newWage = converter1.fromString(event.getNewValue().toString());
                    // 更新 Worker 对象的 wages 属性
                    worker.setWages(newWage);
                } catch (NumberFormatException e) {
                    // 如果转换失败，则恢复旧值并打印错误信息
                    System.err.println("请输入有效的整数");
                    //event.setNewValue(event.getOldValue());
                }
            }
        });


    }

    private void setupPTableView() {

        p_no.setCellValueFactory(new PropertyValueFactory<>("pno"));
        p_no.setCellFactory(TextFieldTableCell.forTableColumn());
        p_no.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            Prisoner prisoner = event.getRowValue();
            prisoner.setPno(event.getNewValue());
        });

        p_name.setCellValueFactory(new PropertyValueFactory<>("pname"));
        p_name.setCellFactory(TextFieldTableCell.forTableColumn());
        p_name.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的w_no属性
            Prisoner prisoner = event.getRowValue();
            prisoner.setPname(event.getNewValue());
        });

        p_sex.setCellValueFactory(new PropertyValueFactory<>("sex"));
        p_sex.setCellFactory(TextFieldTableCell.forTableColumn());
        p_sex.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的w_no属性
            Prisoner prisoner = event.getRowValue();
            prisoner.setSex(event.getNewValue());
        });

        p_id.setCellValueFactory(new PropertyValueFactory<>("idcard"));
        p_id.setCellFactory(TextFieldTableCell.forTableColumn());
        p_id.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的w_no属性
            Prisoner prisoner = event.getRowValue();
            prisoner.setIdcard(event.getNewValue());
        });

        p_birth.setCellValueFactory(new PropertyValueFactory<>("birthday"));
        CustomDateConverter converter = new CustomDateConverter();
        p_birth.setCellFactory(TextFieldTableCell.forTableColumn(converter));
        p_birth.setOnEditCommit(event -> {
            Prisoner prisoner = event.getRowValue();
            LocalDate newBirthDate = converter.fromString(event.getNewValue().toString());
            prisoner.setBirthdayTime(newBirthDate);
        });

        p_crime.setCellValueFactory(new PropertyValueFactory<>("crime"));
        p_crime.setCellFactory(TextFieldTableCell.forTableColumn());
        p_crime.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的w_no属性
            Prisoner prisoner = event.getRowValue();
            prisoner.setCrime(event.getNewValue());
        });

        p_intime.setCellValueFactory(new PropertyValueFactory<>("intime"));
        CustomDateConverter converter1 = new CustomDateConverter();
        p_intime.setCellFactory(TextFieldTableCell.forTableColumn(converter));
        p_intime.setOnEditCommit(event -> {
            Prisoner prisoner = event.getRowValue();
            LocalDate newIntime = converter1.fromString(event.getNewValue().toString());
            prisoner.setIntime(newIntime);
        });

        p_outtime.setCellValueFactory(new PropertyValueFactory<>("outtime"));
        CustomDateConverter converter2 = new CustomDateConverter();
        p_outtime.setCellFactory(TextFieldTableCell.forTableColumn(converter));
        p_outtime.setOnEditCommit(event -> {
            Prisoner prisoner = event.getRowValue();
            LocalDate newOuttime = converter2.fromString(event.getNewValue().toString());
            prisoner.setOuttime(newOuttime);
        });

        p_postion.setCellValueFactory(new PropertyValueFactory<>("postion"));
        p_postion.setCellFactory(TextFieldTableCell.forTableColumn());
        p_postion.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的w_no属性
            Prisoner prisoner = event.getRowValue();
            prisoner.setPostion(event.getNewValue());
        });
    }

    private void setupPWTableView() {
        pw_name.setCellValueFactory(new PropertyValueFactory<>("pworkname"));
        pw_name.setCellFactory(TextFieldTableCell.forTableColumn());
        pw_name.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            Pwork pwork = event.getRowValue();
            pwork.setPworkname(event.getNewValue());
        });

        pw_pno.setCellValueFactory(new PropertyValueFactory<>("pno"));
        pw_pno.setCellFactory(TextFieldTableCell.forTableColumn());
        pw_pno.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的w_no属性
            Pwork pwork = event.getRowValue();
            pwork.setPno(event.getNewValue());
        });

        pw_postion.setCellValueFactory(new PropertyValueFactory<>("pworkposion"));
        pw_postion.setCellFactory(TextFieldTableCell.forTableColumn());
        pw_postion.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的w_no属性
            Pwork pwork = event.getRowValue();
            pwork.setPworkposion(event.getNewValue());
        });

        pw_wtime.setCellValueFactory(new PropertyValueFactory<>("wtime"));
        pw_wtime.setCellFactory(TextFieldTableCell.forTableColumn());
        pw_wtime.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的w_no属性
            Pwork pwork = event.getRowValue();
            pwork.setWtime(event.getNewValue());
        });

        pw_begin.setCellValueFactory(new PropertyValueFactory<>("begintime"));
        CustomDateConverter converter = new CustomDateConverter();
        pw_begin.setCellFactory(TextFieldTableCell.forTableColumn(converter));
        pw_begin.setOnEditCommit(event -> {
            Pwork pwork = event.getRowValue();
            LocalDate newBegintime = converter.fromString(event.getNewValue().toString());
            pwork.setBegintime(newBegintime);
        });

        pw_out.setCellValueFactory(new PropertyValueFactory<>("outtime"));
        CustomDateConverter converter1 = new CustomDateConverter();
        pw_out.setCellFactory(TextFieldTableCell.forTableColumn(converter));
        pw_out.setOnEditCommit(event -> {
            Pwork pwork = event.getRowValue();
            LocalDate newOuttime = converter1.fromString(event.getNewValue().toString());
            pwork.setOuttime(newOuttime);
        });

    }

    private void setupVTableView() {

        v_no.setCellValueFactory(new PropertyValueFactory<>("vno"));
        v_no.setCellFactory(TextFieldTableCell.forTableColumn());
        v_no.setOnEditCommit(event -> {
            //当单元格编辑完成后，更新Worker对象的w_no属性
            Visitor visitor = event.getRowValue();
            visitor.setVno(event.getNewValue());
        });

        v_name.setCellValueFactory(new PropertyValueFactory<>("vname"));
        v_name.setCellFactory(TextFieldTableCell.forTableColumn());
        v_name.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的w_no属性
            Visitor visitor = event.getRowValue();
            visitor.setVname(event.getNewValue());
        });

        v_date.setCellValueFactory(new PropertyValueFactory<>("vadate"));
        CustomDateConverter converter = new CustomDateConverter();
        v_date.setCellFactory(TextFieldTableCell.forTableColumn(converter));
        v_date.setOnEditCommit(event -> {
            Visitor visitor = event.getRowValue();
            LocalDate newVdate = converter.fromString(event.getNewValue().toString());
            visitor.setVadate(newVdate);
        });

        v_pno.setCellValueFactory(new PropertyValueFactory<>("pno"));
        v_pno.setCellFactory(TextFieldTableCell.forTableColumn());
        v_pno.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的w_no属性
            Visitor visitor = event.getRowValue();
            visitor.setPno(event.getNewValue());
        });

        v_wno.setCellValueFactory(new PropertyValueFactory<>("wno"));
        v_wno.setCellFactory(TextFieldTableCell.forTableColumn());
        v_wno.setOnEditCommit(event -> {
            // 当单元格编辑完成后，更新Worker对象的w_no属性
            Visitor visitor = event.getRowValue();
            visitor.setWno(event.getNewValue());
        });

    }


    //给tableviewW建立一个factory，信息需显示在工厂之上
    private void showTable() {


        TableColumn<Worker, String> wnoColumn = (TableColumn<Worker, String>) tableView2.getColumns().get(0);
        wnoColumn.setCellValueFactory(new PropertyValueFactory<>("wno"));

        TableColumn<Worker, String> wnameColumn = (TableColumn<Worker, String>) tableView2.getColumns().get(1);
        wnameColumn.setCellValueFactory(new PropertyValueFactory<>("wname"));

        TableColumn<Worker, String> sexColumn = (TableColumn<Worker, String>) tableView2.getColumns().get(2);
        sexColumn.setCellValueFactory(new PropertyValueFactory<>("sex"));

        TableColumn<Worker, String> idcardColumn = (TableColumn<Worker, String>) tableView2.getColumns().get(3);
        idcardColumn.setCellValueFactory(new PropertyValueFactory<>("idcard"));

        TableColumn<Worker, String> birthdayColumn = (TableColumn<Worker, String>) tableView2.getColumns().get(4);
        birthdayColumn.setCellValueFactory(param -> new SimpleStringProperty(param.getValue().getBirthday().toString()));

        TableColumn<Worker, String> jobtypeColumn = (TableColumn<Worker, String>) tableView2.getColumns().get(5);
        jobtypeColumn.setCellValueFactory(new PropertyValueFactory<>("jobtype"));

        TableColumn<Worker, String> postionColumn = (TableColumn<Worker, String>) tableView2.getColumns().get(6);
        postionColumn.setCellValueFactory(new PropertyValueFactory<>("postion"));

        TableColumn<Worker, Integer> wagesColumn = (TableColumn<Worker, Integer>) tableView2.getColumns().get(7);
        wagesColumn.setCellValueFactory(new PropertyValueFactory<>("wages"));

        TableColumn<Worker, Boolean> operationColumn = (TableColumn<Worker, Boolean>) tableView2.getColumns().get(8);
        operationColumn.setCellValueFactory(param -> {
            Worker worker = param.getValue();
            // 检查Pwork对象是否不为空
            if (worker != null) {
                // 使用Pwork对象的selected属性作为CheckBox的绑定属性
                return worker.selectedProperty();
            }
            return null; // 如果pwork为null，则不显示CheckBox
        });

        operationColumn.setCellFactory(col -> new CheckBoxTableCell<Worker, Boolean>() {
            @Override
            public void updateItem(Boolean item, boolean empty) {
                super.updateItem(item, empty);
                if (empty || item == null) {
                    setGraphic(null);
                    setText("");
                } else {
                    CheckBox checkBox = new CheckBox();
                    checkBox.setSelected(item); // 设置CheckBox的选中状态
                    checkBox.selectedProperty().addListener((observable, oldValue, newValue) -> {
                        // 当CheckBox的选中状态改变时，更新数据模型
                        Worker worker = getTableView().getItems().get(getIndex());
                        worker.setSelected(newValue); // 假设Pwork类中有一个setSelected方法
                    });
                    setGraphic(checkBox);
                }
            }
        });

    }

    private void showPTable() {
        TableColumn<Prisoner, String> pnoColumn = (TableColumn<Prisoner, String>) tableView1.getColumns().get(0);
        pnoColumn.setCellValueFactory(new PropertyValueFactory<>("pno"));

        TableColumn<Prisoner, String> pnameColumn = (TableColumn<Prisoner, String>) tableView1.getColumns().get(1);
        pnameColumn.setCellValueFactory(new PropertyValueFactory<>("pname"));

        TableColumn<Prisoner, String> sexColumn = (TableColumn<Prisoner, String>) tableView1.getColumns().get(2);
        sexColumn.setCellValueFactory(new PropertyValueFactory<>("sex"));

        TableColumn<Prisoner, String> idcardColumn = (TableColumn<Prisoner, String>) tableView1.getColumns().get(3);
        idcardColumn.setCellValueFactory(new PropertyValueFactory<>("idcard"));

        TableColumn<Prisoner, String> birthdayColumn = (TableColumn<Prisoner, String>) tableView1.getColumns().get(4);
        birthdayColumn.setCellValueFactory(param -> new SimpleStringProperty(param.getValue().getBirthdayTime().toString()));

        TableColumn<Prisoner, String> crimeColumn = (TableColumn<Prisoner, String>) tableView1.getColumns().get(5);
        crimeColumn.setCellValueFactory(new PropertyValueFactory<>("crime"));

        TableColumn<Prisoner, String> intimeColumn = (TableColumn<Prisoner, String>) tableView1.getColumns().get(6);
        intimeColumn.setCellValueFactory(param -> new SimpleStringProperty(param.getValue().getIntime().toString()));

        TableColumn<Prisoner, String> outtimeColumn = (TableColumn<Prisoner, String>) tableView1.getColumns().get(7);
        outtimeColumn.setCellValueFactory(param -> new SimpleStringProperty(param.getValue().getOuttime().toString()));


        TableColumn<Prisoner, String> postionColumn = (TableColumn<Prisoner, String>) tableView1.getColumns().get(8);
        postionColumn.setCellValueFactory(new PropertyValueFactory<>("postion"));


        TableColumn<Prisoner, Boolean> operationColumn = (TableColumn<Prisoner, Boolean>) tableView1.getColumns().get(9);
        operationColumn.setCellValueFactory(param -> {
            Prisoner prisoner = param.getValue();
            // 检查Pwork对象是否不为空
            if (prisoner != null) {
                // 使用Pwork对象的selected属性作为CheckBox的绑定属性
                return prisoner.selectedProperty();
            }
            return null; // 如果pwork为null，则不显示CheckBox
        });

        operationColumn.setCellFactory(col -> new CheckBoxTableCell<Prisoner, Boolean>() {
            @Override
            public void updateItem(Boolean item, boolean empty) {
                super.updateItem(item, empty);
                if (empty || item == null) {
                    setGraphic(null);
                    setText("");
                } else {
                    CheckBox checkBox = new CheckBox();
                    checkBox.setSelected(item); // 设置CheckBox的选中状态
                    checkBox.selectedProperty().addListener((observable, oldValue, newValue) -> {
                        // 当CheckBox的选中状态改变时，更新数据模型
                        Prisoner prisoner = getTableView().getItems().get(getIndex());
                        prisoner.setSelected(newValue); // 假设Pwork类中有一个setSelected方法
                    });
                    setGraphic(checkBox);
                }
            }
        });

    }

    private void showVTable() {
        TableColumn<Visitor, String> vnoColumn = (TableColumn<Visitor, String>) VisitorTable.getColumns().get(0);
        vnoColumn.setCellValueFactory(new PropertyValueFactory<>("vno"));

        TableColumn<Visitor, String> vnameColumn = (TableColumn<Visitor, String>) VisitorTable.getColumns().get(1);
        vnameColumn.setCellValueFactory(new PropertyValueFactory<>("vname"));

        TableColumn<Visitor, String> vdateColumn = (TableColumn<Visitor, String>) VisitorTable.getColumns().get(2);
        vdateColumn.setCellValueFactory(param -> new SimpleStringProperty(param.getValue().getVadate().toString()));

        TableColumn<Visitor, String> pnoColumn = (TableColumn<Visitor, String>) VisitorTable.getColumns().get(3);
        pnoColumn.setCellValueFactory(new PropertyValueFactory<>("pno"));

        TableColumn<Visitor, String> wnoColumn = (TableColumn<Visitor, String>) VisitorTable.getColumns().get(4);
        wnoColumn.setCellValueFactory(new PropertyValueFactory<>("wno"));


        TableColumn<Visitor, Boolean> operationColumn = (TableColumn<Visitor, Boolean>) VisitorTable.getColumns().get(5);
        operationColumn.setCellValueFactory(param -> {
            Visitor visitor = param.getValue();
            // 检查Pwork对象是否不为空
            if (visitor != null) {
                // 使用Pwork对象的selected属性作为CheckBox的绑定属性
                return visitor.selectedProperty();
            }
            return null; // 如果pwork为null，则不显示CheckBox
        });

        operationColumn.setCellFactory(col -> new CheckBoxTableCell<Visitor, Boolean>() {
            @Override
            public void updateItem(Boolean item, boolean empty) {
                super.updateItem(item, empty);
                if (empty || item == null) {
                    setGraphic(null);
                    setText("");
                } else {
                    CheckBox checkBox = new CheckBox();
                    checkBox.setSelected(item); // 设置CheckBox的选中状态
                    checkBox.selectedProperty().addListener((observable, oldValue, newValue) -> {
                        // 当CheckBox的选中状态改变时，更新数据模型
                        Visitor visitor = getTableView().getItems().get(getIndex());
                        visitor.setSelected(newValue); // 假设Pwork类中有一个setSelected方法
                    });
                    setGraphic(checkBox);
                }
            }
        });


    }

    private void showPWTable() {
        TableColumn<Pwork, String> pworkColumn = (TableColumn<Pwork, String>) tableViewPW.getColumns().get(0);
        pworkColumn.setCellValueFactory(new PropertyValueFactory<>("pworkname"));

        TableColumn<Pwork, String> pnoColumn = (TableColumn<Pwork, String>) tableViewPW.getColumns().get(1);
        pnoColumn.setCellValueFactory(new PropertyValueFactory<>("pno"));

        TableColumn<Pwork, String> pworkpostionColumn = (TableColumn<Pwork, String>) tableViewPW.getColumns().get(2);
        pworkpostionColumn.setCellValueFactory(new PropertyValueFactory<>("pworkposion"));
        // pworkpostionColumn.setCellValueFactory(param -> new SimpleStringProperty(param.getValue().getVadate().toString()));

        TableColumn<Pwork, String> wtimeColumn = (TableColumn<Pwork, String>) tableViewPW.getColumns().get(3);
        wtimeColumn.setCellValueFactory(new PropertyValueFactory<>("wtime"));
        //  wtimeColumn.setCellValueFactory(new PropertyValueFactory<>("pno"));

        TableColumn<Pwork, String> beginColumn = (TableColumn<Pwork, String>) tableViewPW.getColumns().get(4);
        beginColumn.setCellValueFactory(param -> new SimpleStringProperty(param.getValue().getBegintime().toString()));
        //   beginColumn.setCellValueFactory(new PropertyValueFactory<>("wno"));
        TableColumn<Pwork, String> outColumn = (TableColumn<Pwork, String>) tableViewPW.getColumns().get(5);
        outColumn.setCellValueFactory(param -> new SimpleStringProperty(param.getValue().getOuttime().toString()));
        //  outColumn.setCellValueFactory(new PropertyValueFactory<>("wno"));


        TableColumn<Pwork, Boolean> operationColumn = (TableColumn<Pwork, Boolean>) tableViewPW.getColumns().get(6);
        operationColumn.setCellValueFactory(param -> {
            Pwork pwork = param.getValue();
            // 检查Pwork对象是否不为空
            if (pwork != null) {
                // 使用Pwork对象的selected属性作为CheckBox的绑定属性
                return pwork.selectedProperty();
            }
            return null; // 如果pwork为null，则不显示CheckBox
        });

        operationColumn.setCellFactory(col -> new CheckBoxTableCell<Pwork, Boolean>() {
            @Override
            public void updateItem(Boolean item, boolean empty) {
                super.updateItem(item, empty);
                if (empty || item == null) {
                    setGraphic(null);
                    setText("");
                } else {
                    CheckBox checkBox = new CheckBox();
                    checkBox.setSelected(item); // 设置CheckBox的选中状态
                    checkBox.selectedProperty().addListener((observable, oldValue, newValue) -> {
                        // 当CheckBox的选中状态改变时，更新数据模型
                        Pwork pwork = getTableView().getItems().get(getIndex());
                        pwork.setSelected(newValue); // 假设Pwork类中有一个setSelected方法
                    });
                    setGraphic(checkBox);
                }
            }
        });


    }

    private void loadDataFromDatabase() {
        items.clear();
        Connection conn = null;
        try {
            conn = BaseDao.getConn();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM worker");
            while (rs.next()) {
                String wno = rs.getString("wno");
                String wname = rs.getString("wname");
                String sex = rs.getString("sex");
                String idcard = rs.getString("idcard");
                Date sqlDate = rs.getDate("birthday");
                LocalDate birthday = null;
                if (sqlDate != null) {
                    birthday = sqlDate.toLocalDate();
                }
                String jobtype = rs.getString("jobtype");
                String postion = rs.getString("postion");
                Integer wagesProperty = 0;
                try {
                    int wageValue = rs.getInt("wages");

                    //   System.out.println(wageValue);
                    if (!rs.wasNull()) { // 检查结果是否不是 SQL NULL
                        wagesProperty = wageValue; // 将 int 值设置到 IntegerProperty
                    } else {
                        wagesProperty = 0; // 或者你可以选择设置一个默认值，比如 0
                    }
                } catch (SQLException e) {
                    // 处理可能的 SQLException
                    e.printStackTrace();
                }

                items.add(new Worker(wno, wname, sex, idcard, birthday, jobtype, postion, wagesProperty));

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    private void loadPDataFromDatabase() {
        pitems.clear();
        // IntegerProperty wagesProperty = new SimpleIntegerProperty();
        Connection conn = null;
        try {
            conn = BaseDao.getConn();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM prisoner");
            while (rs.next()) {
                String pno = rs.getString("pno");
                String pname = rs.getString("pname");
                String sex = rs.getString("sex");
                String idcard = rs.getString("idcard");
                Date sqlDate = rs.getDate("birthday");
                LocalDate birthday = null;
                if (sqlDate != null) {
                    birthday = sqlDate.toLocalDate();
                }
                String crime = rs.getString("crime");
                Date sqlDate1 = rs.getDate("intime");
                LocalDate intime = null;
                if (sqlDate1 != null) {
                    intime = sqlDate1.toLocalDate();
                }
                Date sqlDate2 = rs.getDate("outtime");
                LocalDate outtime = null;
                if (sqlDate2 != null) {
                    outtime = sqlDate2.toLocalDate();
                }
                String postion = rs.getString("postion");
                pitems.add(new Prisoner(pno, pname, sex, idcard, birthday, crime, intime, outtime, postion));

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private void loadPWDataFromDatabase() {
        pwitems.clear();
        Connection conn = null;
        try {
            conn = BaseDao.getConn();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM Pwork");
            while (rs.next()) {
                String pworkname = rs.getString("pworkname");
                String pno = rs.getString("pno");
                String pworkpostion = rs.getString("pworkpostion");
                String wtime = rs.getString("wtime");
                Date sqlDate1 = rs.getDate("begintime");
                LocalDate begintime = null;
                if (sqlDate1 != null) {
                    begintime = sqlDate1.toLocalDate();
                }
                Date sqlDate2 = rs.getDate("outtime");
                LocalDate outtime = null;
                if (sqlDate2 != null) {
                    outtime = sqlDate2.toLocalDate();
                }
                pwitems.add(new Pwork(pworkname, pno, pworkpostion, wtime, begintime, outtime));

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    private void loadVDataFromDatabase() {
        vitems.clear();
        // IntegerProperty wagesProperty = new SimpleIntegerProperty();
        Connection conn = null;
        try {
            conn = BaseDao.getConn();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM visitor");
            while (rs.next()) {
                String vno = rs.getString("vno");
                String vname = rs.getString("vname");
                Date sqlDate = rs.getDate("vdate");
                LocalDate vdate = null;
                if (sqlDate != null) {
                    vdate = sqlDate.toLocalDate();
                }
                String pno = rs.getString("pno");

                String wno = rs.getString("wno");

                vitems.add(new Visitor(vno, vname, vdate, pno, wno));

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    @FXML
    public void doquit() {

        changeUI("ui/login.fxml");

    }


    public void dosort(SortEvent<TableView> tableViewSortEvent) {
    }

    @FXML
    public void doPmanage() {
        loadPDataFromDatabase();
        tableView1.setVisible(true);
        pbuttonadd.setVisible(true);
        pbuttonDelete.setVisible(true);

        tableViewPW.setVisible(false);
        pwbuttonadd.setVisible(false);
        PWbuttonDelete.setVisible(false);
        Pselect.setVisible(true);

        tableView2.setVisible(false);
        wbuttonadd.setVisible(false);
        WbuttonDelete.setVisible(false);

        Wselect.setVisible(false);

        VisitorTable.setVisible(false);
        Vbuttonadd1.setVisible(false);
        VbuttonDelete.setVisible(false);

        Vselect.setVisible(false);

    }

    @FXML
    void doPWmanage() {
        loadPWDataFromDatabase();
        tableView1.setVisible(false);
        pbuttonadd.setVisible(false);
        pbuttonDelete.setVisible(false);

        tableViewPW.setVisible(true);
        pwbuttonadd.setVisible(true);
        PWbuttonDelete.setVisible(true);

        Pselect.setVisible(true);

        tableView2.setVisible(false);
        wbuttonadd.setVisible(false);
        WbuttonDelete.setVisible(false);

        Wselect.setVisible(false);

        VisitorTable.setVisible(false);
        Vbuttonadd1.setVisible(false);
        VbuttonDelete.setVisible(false);
        Vselect.setVisible(false);


    }

    @FXML
    public void doVmanage() {
        loadVDataFromDatabase();
        tableView1.setVisible(false);
        pbuttonadd.setVisible(false);
        pbuttonDelete.setVisible(false);

        tableViewPW.setVisible(false);
        pwbuttonadd.setVisible(false);
        PWbuttonDelete.setVisible(false);
        Pselect.setVisible(false);

        tableView2.setVisible(false);
        wbuttonadd.setVisible(false);
        WbuttonDelete.setVisible(false);
        Wselect.setVisible(false);

        VisitorTable.setVisible(true);
        Vbuttonadd1.setVisible(true);
        VbuttonDelete.setVisible(true);
        Vselect.setVisible(true);


    }

    @FXML
    public void doWmanage() {
        loadDataFromDatabase();
        tableView1.setVisible(false);
        pbuttonadd.setVisible(false);
        pbuttonDelete.setVisible(false);

        tableViewPW.setVisible(false);
        pwbuttonadd.setVisible(false);
        PWbuttonDelete.setVisible(false);

        Pselect.setVisible(false);

        tableView2.setVisible(true);
        wbuttonadd.setVisible(true);
        WbuttonDelete.setVisible(true);

        Wselect.setVisible(true);

        VisitorTable.setVisible(false);
        Vbuttonadd1.setVisible(false);
        VbuttonDelete.setVisible(false);

        Vselect.setVisible(true);


    }

    @FXML
    void doPdelete() {
        // 确保UI更新操作在JavaFX应用程序线程上执行
        Platform.runLater(() -> {
            try {
                // 获取数据库连接
                Connection connection = BaseDao.getConn();
                // 开始事务
                connection.setAutoCommit(false);
                try (PreparedStatement deleteStmt = connection.prepareStatement(
                        "DELETE FROM prisoner WHERE pno = ?")) {

                    // 遍历tableViewPW中的所有行
                    for (Prisoner prisoner : pitems) {
                        // 检查CheckBox是否选中
                        if (prisoner.isSelected()) {
                            // 获取选中行的主键值
                            String pno = prisoner.getPno();
                            // 执行删除操作
                            deleteStmt.setString(1, pno);
                            deleteStmt.executeUpdate();
                        }
                    }

                    // 提交事务
                    connection.commit();
                } catch (SQLException e) {
                    // 回滚事务
                    try {
                        connection.rollback();

                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                    e.printStackTrace();
                }

                // 刷新tableViewPW以显示更新的数据
                loadPDataFromDatabase();
                // tableViewPW.refresh();

            } catch (Exception e) {
                e.printStackTrace();
            }
        });

    }

    @FXML
    public void doPWdelete() {
        // 确保UI更新操作在JavaFX应用程序线程上执行
        Platform.runLater(() -> {
            try {
                // 获取数据库连接
                Connection connection = BaseDao.getConn();
                // 开始事务
                connection.setAutoCommit(false);
                try (PreparedStatement deleteStmt = connection.prepareStatement(
                        "DELETE FROM Pwork WHERE pno = ?")) {

                    // 遍历tableViewPW中的所有行
                    for (Pwork pwork : pwitems) {
                        // 检查CheckBox是否选中
                        if (pwork.isSelected()) {
                            // 获取选中行的主键值
                            String pno = pwork.getPno();
                            // 执行删除操作
                            deleteStmt.setString(1, pno);
                            deleteStmt.executeUpdate();
                        }
                    }

                    // 提交事务
                    connection.commit();
                } catch (SQLException e) {
                    // 回滚事务
                    try {
                        connection.rollback();

                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                    e.printStackTrace();
                }

                // 刷新tableViewPW以显示更新的数据
                loadPWDataFromDatabase();
                // tableViewPW.refresh();

            } catch (Exception e) {
                e.printStackTrace();
            }
        });
    }

    @FXML
    void doVdelete() {
        // 确保UI更新操作在JavaFX应用程序线程上执行
        Platform.runLater(() -> {
            try {
                // 获取数据库连接
                Connection connection = BaseDao.getConn();
                // 开始事务
                connection.setAutoCommit(false);
                try (PreparedStatement deleteStmt = connection.prepareStatement(
                        "DELETE FROM visitor WHERE vno = ?")) {

                    // 遍历tableViewPW中的所有行
                    for (Visitor visitor : vitems) {
                        // 检查CheckBox是否选中
                        if (visitor.isSelected()) {
                            // 获取选中行的主键值
                            String vno = visitor.getVno();
                            // 执行删除操作
                            deleteStmt.setString(1, vno);
                            deleteStmt.executeUpdate();
                        }
                    }

                    // 提交事务
                    connection.commit();
                } catch (SQLException e) {
                    // 回滚事务
                    try {
                        connection.rollback();

                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                    e.printStackTrace();
                }

                // 刷新tableViewPW以显示更新的数据
                loadVDataFromDatabase();
                // tableViewPW.refresh();

            } catch (Exception e) {
                e.printStackTrace();
            }
        });

    }

    @FXML
    void doWdelete() {
        // 确保UI更新操作在JavaFX应用程序线程上执行
        Platform.runLater(() -> {
            boolean deletePerformed = false;
            try {
                // 获取数据库连接
                Connection connection = BaseDao.getConn();
                // 开始事务
                connection.setAutoCommit(false);
                try (PreparedStatement deleteStmt = connection.prepareStatement(
                        "DELETE FROM worker WHERE wno = ? ")) {

                    // 遍历tableViewPW中的所有行
                    for (Worker worker : items) {

                        // 检查CheckBox是否选中
                        if (worker.isSelected()) {
                            System.out.println(worker.getJobtype());
                            String jobTypeWithSpaces = worker.getJobtype();
                            if ("管理员".equals(jobTypeWithSpaces.trim())) {
                                // 显示权限不足的弹窗
                                Alert alert = new Alert(Alert.AlertType.WARNING,
                                        "权限不够，不能删除管理员！", ButtonType.OK);
                                alert.showAndWait();
                            } else {
                                // 获取选中行的主键值
                                String wno = worker.getWno();
                                // 执行删除操作
                                deleteStmt.setString(1, wno);
                                deleteStmt.executeUpdate();
                                deletePerformed = true;
                            }
                        }
                    }
                    if (deletePerformed) {
                        connection.commit();  // 提交事务
                    } else {
                        connection.rollback();  // 没有执行删除操作，回滚事务
                    }
                } catch (SQLException e) {
                    // 回滚事务
                    try {
                        connection.rollback();

                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                    e.printStackTrace();
                }
                if (deletePerformed) {
                    // 刷新tableViewPW以显示更新的数据
                    loadVDataFromDatabase();
                    // tableViewPW.refresh();
                }

            } catch (Exception e) {
                e.printStackTrace();
            }
        });

    }

    @FXML
    void doWfinish() {
        tableView3.setVisible(false);
        Wsubmit.setVisible(false);
        WfthisShow.setVisible(false);
    }

    @FXML
    public void doVfinish(ActionEvent actionEvent) {
        VisitorTable1.setVisible(false);
        Vsubmit.setVisible(false);
        VfthisShow.setVisible(false);
    }


    @FXML
    public void doPfinish(ActionEvent actionEvent) {
        tableView11.setVisible(false);
        Psubmit.setVisible(false);
        PfthisShow.setVisible(false);
    }

    @FXML
    void doPWfinish() {
        tableViewPWadd.setVisible(false);
        PWsubmit.setVisible(false);
        PWfthisShow.setVisible(false);
    }


    @FXML
    public void doPselect() {
        changeUI("ui/selectArevise.fxml");
    }

    @FXML
    public void doVselect() {
        changeUI("ui/selectArevise.fxml");

    }

    @FXML
    public void doWselect() {
        changeUI("ui/selectArevise.fxml");

    }

    public void doCount(ActionEvent actionEvent) {
        changeUI("ui/CountTotal.fxml");
    }


    @FXML
    public void doDown() {
        String deviceName = "my_disk";
        String backupPath = "D:\\PManage\\Dump2.bak";

        // 检查备份设备是否已存在
        if (!backupDeviceExists(deviceName)) {
            // 如果备份设备不存在，创建备份设备
            createBackupDevice(deviceName, backupPath);
        } else {
            Alert alert = new Alert(Alert.AlertType.INFORMATION, "备份设备 '" + deviceName + "' 已存在，跳过创建。", ButtonType.OK);
            alert.showAndWait(); // 显示弹窗并等待用户响应
        }

        // 执行数据库备份
        backupDatabase("PManage");
    }

    public void createBackupDevice(String backupDeviceName, String physicalFileName) {
        // 正确的SQL语句，注意EXEC后面不需要再跟USE master
        String sql = "USE master;" +
                "EXEC sp_addumpdevice 'disk', '" + backupDeviceName + "', '" + physicalFileName + "';";
        executeSqlCommand(sql);
    }

    public void backupDatabase(String dbName) {
        // 正确的SQL语句，注意BACKUP DATABASE和BACKUP LOG是分开的命令
        String backupDatabaseSql = "BACKUP DATABASE " + dbName +
                " TO DISK = 'D:\\PManage\\Dump2.bak';";
        String backupLogSql = "BACKUP LOG " + dbName +
                " TO DISK = 'D:\\PManage\\Dump2.bak'" +
                "WITH INIT;";

        // 执行备份数据库的命令
        executeSqlCommand(backupDatabaseSql);
        // 执行备份日志的命令
        executeSqlCommand(backupLogSql);
    }
    // 获取数据库连接的方法
    public static Connection getConn() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }


    public void executeSqlCommand(String sql) {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = getConn(); // 获取数据库连接
            statement = connection.createStatement(); // 创建Statement对象
            statement.execute(sql); // 执行SQL命令
            Alert alert = new Alert(Alert.AlertType.INFORMATION, "数据库备份成功", ButtonType.OK);
            alert.showAndWait();
        } catch (SQLException e) {
            e.printStackTrace();
            Alert alert = new Alert(Alert.AlertType.ERROR, "数据库备份失败: " + e.getMessage(), ButtonType.OK);
            alert.showAndWait();
        } finally {
            // 关闭资源
            try {
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
    public boolean backupDeviceExists(String deviceName) {
        String sql = "EXEC sp_helpdevice '" + deviceName + "'";
        try (Connection connection = BaseDao.getConn();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {

            return resultSet.next(); // 如果存在，返回true
        } catch (SQLException e) {
            e.printStackTrace();
            return false; // 发生异常，返回false
        }
    }
}
